﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Vivasky.T4Helper.Entities;

namespace Vivasky.T4Helper
{
	public static partial class Utils
	{
		#region Consts
		public const string FKSql = @"SELECT
        ThisTable  = FK.TABLE_NAME,
        ThisColumn = CU.COLUMN_NAME,
        OtherTable  = PK.TABLE_NAME,
        OtherColumn = PT.COLUMN_NAME, 
        Constraint_Name = C.CONSTRAINT_NAME,
        Owner = FK.TABLE_SCHEMA
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
    INNER JOIN
        (	
            SELECT i1.TABLE_NAME, i2.COLUMN_NAME
            FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
        ) 
    PT ON PT.TABLE_NAME = PK.TABLE_NAME
    WHERE FK.Table_NAME=@tableName OR PK.Table_NAME=@tableName";


		public const string TABLE_SQL = @"SELECT *
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE'";

		public const string COLUMN_SQL = @"SELECT 
        TABLE_CATALOG AS [Database],
        TABLE_SCHEMA AS Owner, 
        TABLE_NAME AS TableName, 
        COLUMN_NAME AS ColumnName, 
        ORDINAL_POSITION AS OrdinalPosition, 
        COLUMN_DEFAULT AS DefaultSetting, 
        IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType, 
        CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
        DATETIME_PRECISION AS DatePrecision,
        COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
        COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME=@tableName
    ORDER BY OrdinalPosition ASC";
		#endregion

		public static IDataReader GetReader(string sql, string ConnectionString)
		{
			SqlConnection conn = new SqlConnection(ConnectionString);
			SqlCommand cmd = new SqlCommand(sql, conn);
			conn.Open();
			return cmd.ExecuteReader(CommandBehavior.CloseConnection);
		}
		public static SqlCommand GetCommand(string sql, string ConnectionString)
		{
			SqlConnection conn = new SqlConnection(ConnectionString);
			SqlCommand cmd = new SqlCommand(sql, conn);
			conn.Open();
			return cmd;
		}
		static List<SPParam> GetSPParams(string spName, string DatabaseName, string ConnectionString)
		{
			var result = new List<SPParam>();
			string[] restrictions = new string[4] { DatabaseName, null, spName, null };
			using (SqlConnection conn = new SqlConnection(ConnectionString))
			{
				conn.Open();
				var sprocs = conn.GetSchema("ProcedureParameters", restrictions);
				conn.Close();
				foreach (DataRow row in sprocs.Select("", "ORDINAL_POSITION"))
				{
					SPParam p = new SPParam();
					p.SysType = GetSysType(row["DATA_TYPE"].ToString());
					p.DbType = GetDbType(row["DATA_TYPE"].ToString()).ToString();
					p.Name = row["PARAMETER_NAME"].ToString().Replace("@", "");
					p.CleanName = CleanUp(p.Name);
					result.Add(p);
				}


			}
			return result;
		}
		public static List<SP> GetSPs(string db,string ConnectionString)
		{
			var result = new List<SP>();
			//pull the SPs

			DataTable sprocs = null;

			using (SqlConnection conn = new SqlConnection(ConnectionString))
			{
				conn.Open();
				sprocs = conn.GetSchema("Procedures");
				conn.Close();
			}

			foreach (DataRow row in sprocs.Rows)
			{
				string spType = row["ROUTINE_TYPE"].ToString();
				var sp = new SP();
				sp.Name = row["ROUTINE_NAME"].ToString();

				if (spType == "PROCEDURE" & !sp.Name.StartsWith("sp_"))
				{

					sp.CleanName = CleanUp(sp.Name);

					sp.Parameters = GetSPParams(sp.Name,db,ConnectionString);
					result.Add(sp);
				}
			}
			return result;

		}


		public static List<Table> LoadTables(string con)
		{
			var result = new List<Table>();

			//pull the tables in a reader
			using (IDataReader rdr = GetReader(TABLE_SQL,con))
			{
				while (rdr.Read())
				{
					Table tbl = new Table();
					tbl.Name = rdr["TABLE_NAME"].ToString();
					tbl.Schema = rdr["TABLE_SCHEMA"].ToString();
					tbl.Columns = LoadColumns(tbl,con);
					tbl.PrimaryKey = GetPK(tbl.Name,con);
					tbl.CleanName = CleanUp(tbl.Name);
					tbl.ClassName = Inflector.MakeSingular(tbl.CleanName);
					tbl.QueryableName = Inflector.MakePlural(tbl.ClassName);

					//set the PK for the columns
					var pkColumn = tbl.Columns.SingleOrDefault(x => x.Name.ToLower().Trim() == tbl.PrimaryKey.ToLower().Trim());
					if (pkColumn != null)
						pkColumn.IsPK = true;

					tbl.FKTables = LoadFKTables(tbl.Name,con);

					result.Add(tbl);
				}
			}

			foreach (Table tbl in result)
			{
				//loop the FK tables and see if there's a match for our FK columns
				foreach (Column col in tbl.Columns)
				{
					col.IsForeignKey = tbl.FKTables.Any(
						x => x.ThisColumn.Equals(col.Name, StringComparison.InvariantCultureIgnoreCase)
					);
				}
			}
			return result;
		}

		static List<Column> LoadColumns(Table tbl,string con)
		{
			var result = new List<Column>();
			var cmd = GetCommand(COLUMN_SQL,con);
			cmd.Parameters.AddWithValue("@tableName", tbl.Name);

			using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			{
				while (rdr.Read())
				{
					Column col = new Column();
					col.Name = rdr["ColumnName"].ToString();
					col.CleanName = CleanUp(col.Name);
					col.DataType = rdr["DataType"].ToString();
					col.SysType = GetSysType(col.DataType);
					col.DbType = GetDbType(col.DataType);
					col.AutoIncrement = rdr["IsIdentity"].ToString() == "1";
					col.IsNullable = rdr["IsNullable"].ToString() == "YES";
					int.TryParse(rdr["MaxLength"].ToString(), out col.MaxLength);

					result.Add(col);
				}

			}

			return result;
		}

		static List<FKTable> LoadFKTables(string tableName,string con)
		{

			//this is a "bi-directional" scheme
			//which pulls both 1-many and many-1

			var result = new List<FKTable>();
			var cmd = GetCommand(FKSql,con);
			cmd.Parameters.AddWithValue("@tableName", tableName);
			using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			{
				while (rdr.Read())
				{
					FKTable fk = new FKTable();
					string thisTable = rdr["ThisTable"].ToString();

					if (tableName.ToLower() == thisTable.ToLower())
					{
						fk.ThisTable = rdr["ThisTable"].ToString();
						fk.ThisColumn = rdr["ThisColumn"].ToString();
						fk.OtherTable = rdr["OtherTable"].ToString();
						fk.OtherColumn = rdr["OtherColumn"].ToString();

					}
					else
					{
						fk.ThisTable = rdr["OtherTable"].ToString();
						fk.ThisColumn = rdr["OtherColumn"].ToString();
						fk.OtherTable = rdr["ThisTable"].ToString();
						fk.OtherColumn = rdr["ThisColumn"].ToString();

					}

					fk.OtherClass = Inflector.MakeSingular(CleanUp(fk.OtherTable));
					fk.OtherQueryable = Inflector.MakePlural(fk.OtherClass);

					result.Add(fk);
				}
			}
			return result;

		}

		static string GetPK(string table,string con)
		{

			string pk = "";
			DataTable pkTable = new DataTable();
			string sql = @"SELECT KCU.COLUMN_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
        JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
        ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
        WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'
		AND KCU.TABLE_NAME=@tableName";

			var cmd = GetCommand(sql,con);
			cmd.Parameters.AddWithValue("@tableName", table);
			var result = cmd.ExecuteScalar();
			cmd.Dispose();
			if (result != null)
				pk = result.ToString();

			return pk;
		}

		static string GetSysType(string sqlType)
		{
			string sysType = "string";
			switch (sqlType)
			{
				case "bigint":
					sysType = "long";
					break;
				case "smallint":
					sysType = "short";
					break;
				case "int":
					sysType = "int";
					break;
				case "uniqueidentifier":
					sysType = "Guid";
					break;
				case "smalldatetime":
				case "datetime":
					sysType = "DateTime";
					break;
				case "float":
					sysType = "double";
					break;
				case "real":
				case "numeric":
				case "smallmoney":
				case "decimal":
				case "money":
					sysType = "decimal";
					break;
				case "tinyint":
					sysType = "byte";
					break;
				case "bit":
					sysType = "bool";
					break;
				case "image":
				case "binary":
				case "varbinary":
					sysType = "byte[]";
					break;
			}
			return sysType;
		}

		static DbType GetDbType(string sqlType)
		{
			switch (sqlType)
			{
				case "varchar":
					return DbType.AnsiString;
				case "nvarchar":
					return DbType.String;
				case "int":
					return DbType.Int32;
				case "uniqueidentifier":
					return DbType.Guid;
				case "datetime":
					return DbType.DateTime;
				case "bigint":
					return DbType.Int64;
				case "binary":
					return DbType.Binary;
				case "bit":
					return DbType.Boolean;
				case "char":
					return DbType.AnsiStringFixedLength;
				case "decimal":
					return DbType.Decimal;
				case "float":
					return DbType.Double;
				case "image":
					return DbType.Binary;
				case "money":
					return DbType.Currency;
				case "nchar":
					return DbType.String;
				case "ntext":
					return DbType.String;
				case "numeric":
					return DbType.Decimal;
				case "real":
					return DbType.Single;
				case "smalldatetime":
					return DbType.DateTime;
				case "smallint":
					return DbType.Int16;
				case "smallmoney":
					return DbType.Currency;
				case "sql_variant":
					return DbType.String;
				case "sysname":
					return DbType.String;
				case "text":
					return DbType.AnsiString;
				case "timestamp":
					return DbType.Binary;
				case "tinyint":
					return DbType.Byte;
				case "varbinary":
					return DbType.Binary;
				case "xml":
					return DbType.Xml;
				default:
					return DbType.AnsiString;
			}

		}
		static string CleanUp(string tableName)
		{
			string result = tableName;

			//strip blanks
			result = result.Replace(" ", "");

			//put your logic here...
			result=OnTableNameCleanUp(result);

			return result;
		}
		public static string CheckNullable(Column col)
		{
			string result = "";
			if (col.IsNullable && col.SysType != "byte[]" && col.SysType != "string")
				result = "?";
			return result;
		}

		static string OnTableNameCleanUp(string tableName) {
			var retVal = tableName;
			if (null != TableNameCleaned)
			{
				var arg=new TableArgs { TableName = tableName };
				TableNameCleaned(tableName, arg);
				retVal = arg.TableName;
			}
			return retVal;
		}
		public static event EventHandler<TableArgs> TableNameCleaned;

		//Tables to be proccessed
		public static List<Table> MyTables(string con,string[] ExcludeTables,string IncludeTables)
		{
			var tables = LoadTables(con);
			tables = tables.Where(x => { return !ExcludeTables.Contains(x.Name); }).ToList();
			if (IncludeTables.Length > 0)
			{
				tables = tables.Where(x => IncludeTables.Contains(x.Name)).ToList();
			};
			return tables;
		}

		//SPs to be proccessed
		public static List<SP> MySPs(string db,string con,string[] ExcludeSPs,string IncludeSPs)
		{
			var sps = GetSPs(db, con);
			sps = sps.Where(x => { return !ExcludeSPs.Contains(x.Name); }).ToList();
			if (IncludeSPs.Length > 0)
			{
				sps = sps.Where(x => IncludeSPs.Contains(x.Name)).ToList();
			};
			return sps;
		}
	}
}
